********************************************************************************
*   Project: The Diabetes care continuum in Venezuela						   *
*   Task: Table A1: Compare complete BL DM pop with FU DM pop	               *
*	Author: Dina Goodman (Harvard University)      							   *
*	Code check:  				  											   *
*   Date: 23 July 2021													 	   *
*	Last updated: 					   				   *
********************************************************************************
*Set globals for entire anaylsis
global Data "/Users/dinagoodman/Dropbox/Harvard/Dissertation/Paper 3/Data"
global Results "/Users/dinagoodman/Dropbox/Harvard/Dissertation/Paper 3/Results"
global Code "/Users/dinagoodman/Dropbox/Harvard/Dissertation/Paper 3/Code"

*Prep data set thats FULL EVESCAM 1 with cleaned EVESCAM 2
cd "$Data"
use "Paper 3_Final BL Dataset_8.12.21.dta", clear
drop _merge dup
merge m:m Code using "Paper 3_Final FU Dataset_8.7.21.dta"

sort Code
quietly by Code: gen dup = cond(_N==1,0,_n)
drop if dup>0

/*Table 1 Code*/
//TOTAL POPULATION BASELINE CHARACTERISTICS
gen fupop=1 if _merge==3 | _merge==2
replace fupop=0 if _merge==1

*drop non diabetics
replace clindia_self=1 if clindia_self_bl==1

drop if clindia_self_bl==0
drop if clindia_self==0
*prep some variables that didn't match

global vars Gender agecat ses urban overweightobese htn highLDL
foreach var in $vars {
tab `var' fupop, col chi2
}

*check for ps - exact if any cells <=5
tab agecat fupop, row exact
tab ses fupop, row chi2
tab urban fupop, col chi2
tab overweightobese fupop, col exact
tab htn_bl fupop, col chi2
tab highldl_bl fupop, col chi2

*Add headers
	cd "$Results"
	putexcel set "tablea2_output.xls", replace
	
	putexcel B1=("Total DM Pop")
	putexcel B2=("n")
	putexcel C2=("%")
	putexcel D1=("FU FM Pop")
	putexcel D2=("n")
	putexcel E2=("%")
	

*Sex*
putexcel A3=("Female")
local row=3
	foreach var in Gender {
		*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
		*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}		
*Age category at baseline
putexcel A5=("Age Category")
	foreach var in agecat {
			*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
		*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}	

*SES
putexcel A`row'=("SES")
	foreach var in ses {
		*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
		*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}	
*Urban
putexcel A`row'=("Urban")
	foreach var in urban {
		*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
		*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}	
			
*BMI
putexcel A`row'=("Overweight")
	foreach var in overweightobese {
	*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
	*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}	
*Hypertension
putexcel A`row'=("HTN")
	foreach var in htn {
	*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
	*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}	

*High LDL
putexcel A`row'=("High LDL")
	foreach var in highLDL {
	*for baseline only
		tabulate `var' if fupop==0, matcell(`var'freq) matrow(`var'names)
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
	*for follow-up only
		tabulate `var' if fupop==1, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}	
